import akshare as ak
import numpy as np
import pandas as pd
import pymysql
import re
import time
import json
import os


with open(os.path.dirname(os.path.realpath(__file__))+'/config/mysql.json','r',encoding='utf8')as fp:
    json_data = json.load(fp)
    db = pymysql.connect(json_data["url"], json_data["user"], json_data["pwd"], json_data["database"])

def capture():
    np.set_printoptions(threshold=np.inf)

    # stock_em_yjyg_df = ak.stock_em_yjyg(date="2020-03-31")
    # print(stock_em_yjyg_df)
    # 加了这一行那表格的一行就不会分段出现了
    pd.set_option('display.width', 1000)
    # 显示所有列
    pd.set_option('display.max_columns', None)
    # 显示所有行
    pd.set_option('display.max_rows', None)

    reg = re.compile(r'\w{4}-12-31')

    # db = pymysql.connect("localhost", "root", "root", "lw")
    cursor = db.cursor()
    cursor.execute("select * from t_gp_sssj")
    gp_list = cursor.fetchall()

    for row in gp_list:
        code = row[1]
        print(code)
        cursor = db.cursor()
        cursor.execute("select * from t_gp_cwzb  where `股票代码`= '%s' and `日期`='2019-12-31'" % (code))
        already_cap_list = cursor.fetchall()
        if already_cap_list:
            continue
        else:
            try:
                stock_financial_analysis_indicator_df = ak.stock_financial_analysis_indicator(stock=code)
                # print(stock_financial_analysis_indicator_df)
                cursor.execute("delete  from t_gp_cwzb  where `股票代码`= '%s' " % (code))
                for index, cwzb_row in stock_financial_analysis_indicator_df.iterrows():

                    if (reg.match(index)):
                        insert_cwzb(cursor,cwzb_row,code,index)

                db.commit()
            except IOError as e:
                print('except:',e)
                capture()
            finally:
                time.sleep(5)


    # db.close()


def insert_cwzb(cursor, cwzb_row,*args):
    sql = "INSERT INTO `t_gp_cwzb`(`股票代码`, `日期`," \
          + " `摊薄每股收益(元)`, `加权每股收益(元)`," \
          + " `每股收益_调整后(元)`, `扣除非经常性损益后的每股收益(元)`, `每股净资产_调整前(元)`, " \
          + "`每股净资产_调整后(元)`, `每股经营性现金流(元)`, `每股资本公积金(元)`, `每股未分配利润(元)`," \
          + " `调整后的每股净资产(元)`, `总资产利润率(%%)`, `主营业务利润率(%%)`, `总资产净利润率(%%)`," \
          + " `成本费用利润率(%%)`, `营业利润率(%%)`, `主营业务成本率(%%)`, `销售净利率(%%)`, `股本报酬率(%%)`, " \
          + "`净资产报酬率(%%)`, `资产报酬率(%%)`, `销售毛利率(%%)`, `三项费用比重`, `非主营比重`, `主营利润比重`," \
          + " `股息发放率(%%)`, `投资收益率(%%)`, `主营业务利润(元)`, `净资产收益率(%%)`, `加权净资产收益率(%%)`, " \
          + "`扣除非经常性损益后的净利润(元)`, `主营业务收入增长率(%%)`, `净利润增长率(%%)`, `净资产增长率(%%)`, " \
          + "`总资产增长率(%%)`, `应收账款周转率(次)`, `应收账款周转天数(天)`, `存货周转天数(天)`, `存货周转率(次)`, " \
          + "`固定资产周转率(次)`, `总资产周转率(次)`, `总资产周转天数(天)`, `流动资产周转率(次)`, `流动资产周转天数(天)`," \
          + " `股东权益周转率(次)`, `流动比率`, `速动比率`, `现金比率(%%)`, `利息支付倍数`, `长期债务与营运资金比率(%%)`," \
          + " `股东权益比率(%%)`, `长期负债比率(%%)`, `股东权益与固定资产比率(%%)`, `负债与所有者权益比率(%%)`," \
          + " `长期资产与长期资金比率(%%)`, `资本化比率(%%)`, `固定资产净值率(%%)`, `资本固定化比率(%%)`, `产权比率(%%)`," \
          + " `清算价值比率(%%)`, `固定资产比重(%%)`, `资产负债率(%%)`, `总资产(元)`, `经营现金净流量对销售收入比率(%%)`," \
          + " `资产的经营现金流量回报率(%%)`, `经营现金净流量与净利润的比率(%%)`, `经营现金净流量对负债比率(%%)`, " \
          + "`现金流量比率(%%)`, `短期股票投资(元)`, `短期债券投资(元)`, `短期其它经营性投资(元)`, `长期股票投资(元)`," \
          + " `长期债券投资(元)`, `长期其它经营性投资(元)`, `1年以内应收帐款(元)`, `1_2年以内应收帐款(元)`," \
          + " `2_3年以内应收帐款(元)`, `3年以内应收帐款(元)`, `1年以内预付货款(元)`, `1_2年以内预付货款(元)`, " \
          + "`2_3年以内预付货款(元)`, `3年以内预付货款(元)`, `1年以内其它应收款(元)`, `1_2年以内其它应收款(元)`," \
          + " `2_3年以内其它应收款(元)`, `3年以内其它应收款(元)`) VALUES (" \
          + print_cs_str(87) + ")"
    param=(args[0],args[1],
       cwzb_row['摊薄每股收益(元)'], cwzb_row['加权每股收益(元)'],
       cwzb_row['每股收益_调整后(元)'], cwzb_row['扣除非经常性损益后的每股收益(元)'], cwzb_row['每股净资产_调整前(元)'],
       cwzb_row['每股净资产_调整后(元)'], cwzb_row['每股经营性现金流(元)'], cwzb_row['每股资本公积金(元)'], cwzb_row['每股未分配利润(元)'],
       cwzb_row['调整后的每股净资产(元)'],cwzb_row['总资产利润率(%)'],cwzb_row['主营业务利润率(%)'],cwzb_row['总资产净利润率(%)'],
       cwzb_row['成本费用利润率(%)'],cwzb_row['营业利润率(%)'],cwzb_row['主营业务成本率(%)'],cwzb_row['销售净利率(%)'],cwzb_row['股本报酬率(%)'],
       cwzb_row['净资产报酬率(%)'],cwzb_row['资产报酬率(%)'],cwzb_row['销售毛利率(%)'],cwzb_row['三项费用比重'],cwzb_row['非主营比重'],cwzb_row['主营利润比重'],
       cwzb_row['股息发放率(%)'],cwzb_row['投资收益率(%)'],cwzb_row['主营业务利润(元)'],cwzb_row['净资产收益率(%)'],cwzb_row['加权净资产收益率(%)'],
       cwzb_row['扣除非经常性损益后的净利润(元)'],cwzb_row['主营业务收入增长率(%)'],cwzb_row['净利润增长率(%)'],cwzb_row['净资产增长率(%)'],
       cwzb_row['总资产增长率(%)'],cwzb_row['应收账款周转率(次)'],cwzb_row['应收账款周转天数(天)'],cwzb_row['存货周转天数(天)'],cwzb_row['存货周转率(次)'],
       cwzb_row['固定资产周转率(次)'],cwzb_row['总资产周转率(次)'],cwzb_row['总资产周转天数(天)'],cwzb_row['流动资产周转率(次)'],cwzb_row['流动资产周转天数(天)'],
       cwzb_row['股东权益周转率(次)'],cwzb_row['流动比率'],cwzb_row['速动比率'],cwzb_row['现金比率(%)'],cwzb_row['利息支付倍数'],cwzb_row['长期债务与营运资金比率(%)'],
       cwzb_row['股东权益比率(%)'],cwzb_row['长期负债比率(%)'],cwzb_row['股东权益与固定资产比率(%)'],cwzb_row['负债与所有者权益比率(%)'],
       cwzb_row['长期资产与长期资金比率(%)'],cwzb_row['资本化比率(%)'],cwzb_row['固定资产净值率(%)'],cwzb_row['资本固定化比率(%)'],cwzb_row['产权比率(%)'],
       cwzb_row['清算价值比率(%)'],cwzb_row['固定资产比重(%)'],cwzb_row['资产负债率(%)'],cwzb_row['总资产(元)'],cwzb_row['经营现金净流量对销售收入比率(%)'],
       cwzb_row['资产的经营现金流量回报率(%)'],cwzb_row['经营现金净流量与净利润的比率(%)'],cwzb_row['经营现金净流量对负债比率(%)'],
       cwzb_row['现金流量比率(%)'],cwzb_row['短期股票投资(元)'],cwzb_row['短期债券投资(元)'],cwzb_row['短期其它经营性投资(元)'],cwzb_row['长期股票投资(元)'],
       cwzb_row['长期债券投资(元)'],cwzb_row['长期其它经营性投资(元)'],cwzb_row['1年以内应收帐款(元)'],cwzb_row['1-2年以内应收帐款(元)'],
       cwzb_row['2-3年以内应收帐款(元)'],cwzb_row['3年以内应收帐款(元)'],cwzb_row['1年以内预付货款(元)'],cwzb_row['1-2年以内预付货款(元)'],
       cwzb_row['2-3年以内预付货款(元)'],cwzb_row['3年以内预付货款(元)'],cwzb_row['1年以内其它应收款(元)'],cwzb_row['1-2年以内其它应收款(元)'],
       cwzb_row['2-3年以内其它应收款(元)'], cwzb_row['3年以内其它应收款(元)'])
    # print(sql % param)
    cursor.execute(sql % param)


def print_cs_str(len):
    n = 0
    s = ''
    while n < len:
        if n == len - 1:
            s += "'%s' "
        else:
            s += "'%s', "
        n += 1
    return s


# stock_financial_analysis_indicator_df = ak.stock_financial_analysis_indicator(stock="600004")
# print(stock_financial_analysis_indicator_df)
if __name__ == "__main__":
    # print(print_cs_str(5))
    capture()